<?php
/*
 * 控制台
*/
header("Content-Type: text/html;charset=UTF-8");
class Console extends CI_Controller {
	function __construct() {
		parent::__construct ();
		$this->load->helper ( 'url' );
	}

	public function index($offset = '') {
		$user_name=$this->session->userdata('username');
		if($user_name){
			$data ['page_title'] = '控制台 — 昊美科技CRM系统';
			$data ['class'] = 'cur';

			$data['after_seven_days_schedules'] = $this->get_seven_days_schedule();/*7天内日程*/
			$data['extended_schedules'] = $this->get_extended();
			$data['customers'] = $this->new_customers();
			$data['opportunitys'] = $this->new_opportunity();
			$data['contacters'] = $this->new_contacter();
			$data['contracts'] = $this->new_contract();
			$data['receivables'] = $this->receivables();
			$data['before_receivables'] = $this->before_receivables();

			$this->load->view ( 'console_index', $data );
		}else{
			$this->load->view ( 'login');
		}
	}

	public function get_seven_days_schedule(){ /*7天内日程*/
		date_default_timezone_set('PRC');
		$now=date('Y-m-d', strtotime('now'));

		$newdate = strtotime($now) + (3600 * 24 * 7);
		$after_seven_days = date('Y-m-d',$newdate);
		$user_id =$this->session->userdata('id');
		$query = $this->db->query("SELECT * FROM schedule where (execution_time between '$now' and  '$after_seven_days') and user_id = $user_id order by execution_time asc");
		return $query->result();
	}

	public function get_extended(){ /*超期未完成的日程*/
		date_default_timezone_set('PRC');
		$now=date('Y-m-d', strtotime('now'));
		$user_id =$this->session->userdata('id');
		$query = $this->db->query("SELECT * FROM schedule where (execution_time < '$now' and status <> '完成')  and user_id = $user_id  order by execution_time asc");

		return $query->result();
	}

	public function new_customers(){ /*获取新建客户*/
		$now=date('Y-m-d', strtotime('now'));

		$newdate = strtotime($now) - (3600 * 24 * 30);
		$before_ten_days = date('Y-m-d',$newdate);
		$user_id =$this->session->userdata('id');

		$now = strtotime($now) + (3600 * 24 * 1);
		$now = date('Y-m-d',$now);

		$sql="SELECT cu.*,re.district as district
			  FROM customer as cu join region as re on cu.region_id = re.id
			  where (cu.create_time >= '$before_ten_days' and cu.create_time <= '$now') and cu.user_id = $user_id order by cu.create_time desc";
		$query = $this->db->query($sql);
		
		return $query->result();
	}

	public function new_opportunity(){ /*获取新建销售机会*/
		date_default_timezone_set('PRC');
		$now=date('Y-m-d', strtotime('now'));

		$newdate = strtotime($now) - (3600 * 24 * 30);
		$before_ten_days = date('Y-m-d',$newdate);
		$user_id =$this->session->userdata('id');

		$now = strtotime($now) + (3600 * 24 * 1);
		$now = date('Y-m-d',$now);

		$sql="SELECT customer.customer_name as customer_name, customer.user_id as customer_user_id, opportunity.id as opportunity_id, opportunity.*, contacter.id as contacter_id ".
			 "FROM (customer) JOIN opportunity ON opportunity.customer_id = customer.id JOIN contacter ON contacter.id = opportunity.contacter_id ".
			 "WHERE (opportunity.create_time >= '$before_ten_days' and opportunity.create_time <= '$now') and customer.user_id = '$user_id' ORDER BY customer.update_time desc";
		$query = $this->db->query($sql);

		return $query->result();
	}

	public function new_contacter(){ /*获取新建联系人*/
		date_default_timezone_set('PRC');
		$now=date('Y-m-d', strtotime('now'));

		$newdate = strtotime($now) - (3600 * 24 * 30);
		$before_ten_days = date('Y-m-d',$newdate);
		$user_id =$this->session->userdata('id');

		$now = strtotime($now) + (3600 * 24 * 1);
		$now = date('Y-m-d',$now);

		$sql="select cu.customer_name,con.* from contacter as con join customer as cu on cu.id = con.customer_id where (con.create_time >= '$before_ten_days' and con.create_time <= '$now') and cu.user_id = $user_id";
		$query = $this->db->query($sql);

		return $query->result();
	}

	public function new_contract(){ /*获取新建合同*/
		date_default_timezone_set('PRC');
		$now=date('Y-m-d', strtotime('now'));

		$newdate = strtotime($now) - (3600 * 24 * 30);
		$before_ten_days = date('Y-m-d',$newdate);
		$user_id =$this->session->userdata('id');

		$now = strtotime($now) + (3600 * 24 * 1);
		$now = date('Y-m-d',$now);

		$sql="select * from contract where (create_time >= '$before_ten_days' and create_time <= '$now') and owner_id = $user_id order by create_time desc";
		$query = $this->db->query($sql);

		return $query->result();
	}

	public function receivables(){ /*应收款(30天内或已经过期) 未来30天*/
		date_default_timezone_set('PRC');
		$now=date('Y-m-d', strtotime('now'));

		$newdate = strtotime($now) + (3600 * 24 * 30);
		$thirty_days = date('Y-m-d',$newdate);
		$user_id =$this->session->userdata('id');

		$condition='payment_plan.id as id,payment_plan.money as money,payment_plan.payment_time as payment_time,payment_plan.back as back,payment_plan.stages as stages,customer.customer_name as customer_name';
		$sql="select $condition from payment_plan join customer on payment_plan.customer_id = customer.id where (payment_plan.payment_time >= '$now' and payment_plan.payment_time <= '$thirty_days') and payment_plan.owner_id = $user_id order by payment_plan.payment_time asc";
		$query = $this->db->query($sql);

		return $query->result();
	}

	public function before_receivables(){ /*应收款(30天内或已经过期) 今天之前的回款计划*/
		date_default_timezone_set('PRC');
		$now=date('Y-m-d', strtotime('now'));
		$user_id =$this->session->userdata('id');

		$condition='payment_plan.id as id,payment_plan.money as money,payment_plan.payment_time as payment_time,payment_plan.back as back,payment_plan.stages as stages,customer.customer_name as customer_name';
		$sql="select $condition from payment_plan join customer on payment_plan.customer_id = customer.id where (payment_plan.payment_time < '$now')  and payment_plan.owner_id = $user_id order by payment_plan.payment_time asc";
		$query = $this->db->query($sql);

		return $query->result();
	}

	public function personal_information(){ /*个人信息*/
		/*$data ['page_title'] = '控制台 —— 昊美科技CRM系统';
		$data ['class10'] = 'cur';
		$user_id =$this->session->userdata('id');

		$query = $this->db->query("SELECT * FROM user where id = $user_id");
		$row = $query->row();
		$data['personal'] = $row;*/
		$this->load->view ( 'personal_information_edit' );
	}
}
?>